package week05.q10;

import java.sql.*;

/**
 * @author lv
 * @date 2021/4/17 21:29
 */
public class Test3 {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        long startTime = System.currentTimeMillis();
        HikariCP hikariCP = new HikariCP();
        Connection conn = hikariCP.getConnection();
        //3.通过数据库的连接操作数据库，实现增删改查（使用Statement类）
        String sql = "insert into user(name,password) values(?,?)";
        conn.setAutoCommit(false);//将自动提交关闭
        PreparedStatement pst = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
        pst.setString(1,"xiaoming");
        pst.setString(2, "123");
//        pst.execute();
        pst.addBatch();
//        pst.executeBatch();

        ResultSet rs3 = pst.getGeneratedKeys();
        int id3 = 1;
        while (rs3.next()){
            id3  = rs3.getInt(1);
            System.out.println(id3);
        }
        sql = " select * from user order by id desc limit 1";
        int id1 = 1;
        ResultSet rs1 = pst.executeQuery(sql);
        while (rs1.next()){
            id1 = rs1.getInt("id");
            System.out.println(id1);
        }
        sql = "update user set name = 'lv' where id = " + id1;
//        pst.executeUpdate(sql);
        pst.addBatch(sql);
        pst.executeBatch();

//        pst.executeBatch();
        sql = " select * from user order by id limit 1";
        int id2 = 1;
        ResultSet rs2 = pst.executeQuery(sql);
        while (rs2.next()){
            id2 = rs2.getInt("id");
            System.out.println(id2);
        }

        sql = " delete from user where id = " + id2;
        pst.executeUpdate(sql);
        conn.commit();//执行完后，手动提交事务
        //conn.setAutoCommit(true);//再把自动提交打开，避免影响其他需要自动提交的操作
        //关闭资源
        hikariCP.close();
        System.out.println(System.currentTimeMillis()-startTime);
    }
}
